package com.buaa.service.impl;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.solr.client.solrj.SolrClient;
import org.apache.solr.client.solrj.SolrQuery;
import org.apache.solr.client.solrj.response.Group;
import org.apache.solr.client.solrj.response.GroupCommand;
import org.apache.solr.client.solrj.response.GroupResponse;
import org.apache.solr.client.solrj.response.QueryResponse;
import org.apache.solr.common.SolrDocument;
import org.apache.solr.common.SolrDocumentList;
import org.apache.solr.common.params.GroupParams;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.buaa.dao.InstrumentDao;
import com.buaa.service.InstrumentService;
import com.buaa.utils.JacksonUtil;
import com.buaa.utils.SolrUtil;

/**
 * Title: 仪器Service实现 Description: Company: BUAA
 *
 * @author 伟宏
 * @date 2017年1月16日
 */
@Service
public class InstrumentServiceImpl implements InstrumentService {

	@Autowired
	private InstrumentDao instrumentDao;
	// @Autowired
	// private InstrumentAttDao instrumentAttDao;

	@Override
	public String sumInfo(String loctype) {
		String sql = null;
		switch (loctype) {
		case "province":
			sql = "SELECT '黑龙江省',COUNT(*),128.04741,47.35659 FROM instrument ";
			break;
		case "city":
			sql = "SELECT city,COUNT(*),city.lng,city.lat FROM instrument ins,city WHERE ins.city = city.city_name GROUP BY city,city.lng,city.lat";
			break;
		case "xian": // 前端已屏蔽
			sql = "select count(*) from instrument";
			break;
		case "danwei":
			sql = "SELECT ins.unit_name,COUNT(*),ins2.lng,ins2.lat FROM instrument ins,institution ins2 WHERE ins.unit_code = ins2.code GROUP BY ins.unit_name,ins2.lng,ins2.lat";
			break;
		}
		List<Object[]> list = instrumentDao.common2(sql);
		String res = "";
		Object[] objects = null;
		if (list != null) {
			for (int i = 0; i < list.size(); i++) {
				objects = list.get(i);
				if (i == list.size() - 1) {
					res += objects[2] + "," + objects[3] + "," + objects[1] + "," + objects[0] + "," + loctype;
				} else {
					res += objects[2] + "," + objects[3] + "," + objects[1] + "," + objects[0] + "," + loctype + "|";
				}
			}
		}
		return res;
	}

	@Override
	public Map<String, Long> getCount(String quyu, String loctype, String zhibiao) {
		String sql = "";
		if (zhibiao.equals("type")) {
			sql = "select count(*),inst.type_name from instrument ins,instrument_type inst ";
			if (loctype.equals("city"))
				sql += " where ins.type = inst.type_id and city='" + quyu + "'";
			else if (loctype.equals("xian"))
				sql += " where ins.type = inst.type_id and county='" + quyu + "'";
			else if (loctype.equals("danwei"))
				sql += ",institution where ins.type = inst.type_id and ins.unit_code = institution.code and unit_name='" + quyu + "'";
			else
				sql += " where ins.type = inst.type_id ";
			sql += " group by inst.type_name order by count(*) desc ";
		} else {
			sql = "select count(*),instrument." + zhibiao + " from instrument ";
			if (loctype.equals("city"))
				sql += " where city='" + quyu + "'";
			else if (loctype.equals("xian"))
				sql += " where county='" + quyu + "'";
			else if (loctype.equals("danwei"))
				sql += ",institution where instrument.unit_code = institution.code and unit_name='" + quyu + "'";
			sql += " group by instrument." + zhibiao + " order by count(*) desc ";
		}
		System.out.println(sql);

		Map<String, Long> metaData = new LinkedHashMap<String, Long>();
		List<Object[]> list = instrumentDao.common2(sql);
		if (list == null)
			return metaData;
		for (Object[] objects : list) {
			metaData.put(objects[1].toString(), Math.round(Double.parseDouble(objects[0].toString())));
		}
		return metaData;
	}

	private SolrQuery processQuery(SolrQuery query, String yqmc, String ssdw, String yqmccxfs, String ssdwcxfs, String yqlx, String yqcd) {
		// 输入查询条件
		if (StringUtils.isNotBlank(yqmc)) {
			switch (yqmccxfs) {
			case "1": // 全匹配
				query.setQuery("instrument_name_s:" + yqmc);
				break;
			case "2": // 模糊匹配
				query.setQuery("instrument_name:" + yqmc);
				break;
			case "3": // 局部全匹配
				query.setQuery("instrument_name_s:*" + yqmc + "*");
				break;
			default:
				query.setQuery("*:*");
				break;
			}
		} else {
			query.setQuery("*:*");
		}
		if (StringUtils.isNotBlank(ssdw)) {
			switch (ssdwcxfs) {
			case "1": // 全匹配
				query.addFilterQuery("institution_name_s:" + ssdw);
				break;
			case "2": // 模糊匹配
				query.addFilterQuery("institution_name:" + ssdw);
				break;
			case "3": // 局部全匹配
				query.addFilterQuery("institution_name_s:*" + ssdw + "*");
				break;
			}
		}
		if (StringUtils.isNotBlank(yqlx))
			query.addFilterQuery("instrument_type:" + yqlx);
		if (StringUtils.isNotBlank(yqcd))
			query.addFilterQuery("instrument_home:" + yqcd);
		return query;
	}

	@Override
	public String contentSearch2(String loctype, String yqmc, String ssdw, String yqmccxfs, String ssdwcxfs, String yqlx, String yqcd) {
		SolrClient solrClient = SolrUtil.getHttpSolrClient("instrument");
		SolrQuery query = new SolrQuery();
		QueryResponse response = null;
		String res = "";

		query = processQuery(query, yqmc, ssdw, yqmccxfs, ssdwcxfs, yqlx, yqcd);
		// 设置分页信息
		query.setStart(0);
		query.setRows(Integer.MAX_VALUE);
		if (StringUtils.equals(loctype, "province")) {
			// 设置显示的Field的域集合
			query.setFields("instrument_id");
			// 执行查询并返回结果
			System.out.println("contentSearch:province:\t" + query);
			try {
				response = solrClient.query(query);
				solrClient.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			long count = 0L;
			// 获取匹配结果总数
			if (response != null) {
				count = response.getResults().getNumFound();
			}
			return "黑龙江省," + count + ",127.91077,48.734684,province";
		} else {
			String lng = null;
			String lat = null;
			String groupIndex = null;
			switch (loctype) {
			case "city":
				lng = "city_lng";
				lat = "city_lat";
				groupIndex = "institution_city";
				break;
			case "xian":
				lng = "county_lng";
				lat = "county_lat";
				groupIndex = "institution_county";
				break;
			case "danwei":
				lng = "institution_lng";
				lat = "institution_lat";
				groupIndex = "institution_name_s";
				break;
			default:
				return res;
			}
			// 设置显示的Field的域集合
			query.setFields(lng + "," + lat);
			// 设置分类信息 （默认每个group显示一条）
			query.setParam(GroupParams.GROUP, true);
			query.setParam(GroupParams.GROUP_FIELD, groupIndex);
			// 执行查询并返回结果
			System.out.println("contentSearch:" + loctype + ":\t" + query);
			try {
				response = solrClient.query(query);
				solrClient.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (response != null) {
				GroupResponse groupResponse = response.getGroupResponse();
				if (groupResponse != null) {
					List<GroupCommand> groupList = groupResponse.getValues();
					for (GroupCommand groupCommand : groupList) {
						List<Group> groups = groupCommand.getValues();
						for (Group group : groups) {
							System.out.println("contentSearch:类别总数：" + group.getResult().getNumFound());
							SolrDocumentList list = group.getResult();
							res += group.getGroupValue() + "," + group.getResult().getNumFound() + "," + list.get(0).get(lng) + "," + list.get(0).get(lat) + "," + loctype + "|";
						}
					}
				}
			}
			return res;
		}
	}

	private String processSql(String sql, String yqmc, String ssdw, String yqmccxfs, String yqlx, String yqcd) {
		if (!yqmc.equals(""))
			if (yqmccxfs.equals("1"))
				sql += " and ins1.name_zh LIKE '" + yqmc + "'";
			else
				sql += " and ins1.name_zh LIKE '%" + yqmc + "%'";
		if (!ssdw.equals(""))
			sql += " and ins1.unit_name like '" + ssdw + "'";
		if (!yqlx.equals(""))
			sql += " and inst.type_name like '" + yqlx + "'";
		if (!yqcd.equals(""))
			sql += " and ins1.home like '" + yqcd + "'";
		return sql;
	}

	@Override
	public String contentSearch(String loctype, String yqmc, String ssdw, String yqmccxfs, String yqlx, String yqcd) {
		String res = "";
		String sql = null;
		switch (loctype) {
		case "province":
			sql = "SELECT COUNT(*),'黑龙江省',127.91077,48.734684 FROM instrument ins1,institution ins2,instrument_type inst WHERE ins1.unit_code = ins2.code and ins1.type = inst.type_id ";
			sql = processSql(sql, yqmc, ssdw, yqmccxfs, yqlx, yqcd);
			break;
		case "city":
			sql = "SELECT COUNT(*),ins1.city,city.lng,city.lat FROM instrument ins1,institution ins2,city,instrument_type inst WHERE ins1.unit_code = ins2.code and ins1.city = city_name and ins1.type = inst.type_id ";
			sql = processSql(sql, yqmc, ssdw, yqmccxfs, yqlx, yqcd);
			sql += " GROUP BY ins1.city,city.lng,city.lat";
			break;
		case "xian":
			sql = "SELECT COUNT(*),ins2.county,county.lng,county.lat FROM instrument ins1,institution ins2,county,instrument_type inst WHERE ins1.unit_code = ins2.code and ins2.county = county_name and ins1.type = inst.type_id ";
			sql = processSql(sql, yqmc, ssdw, yqmccxfs, yqlx, yqcd);
			sql += " GROUP BY ins2.county,county.lng,county.lat";

			break;
		case "danwei":
			sql = "SELECT COUNT(*),ins2.name,ins2.lng,ins2.lat FROM instrument ins1,institution ins2,instrument_type inst WHERE ins1.unit_code = ins2.code and ins1.type = inst.type_id ";
			sql = processSql(sql, yqmc, ssdw, yqmccxfs, yqlx, yqcd);
			sql += " GROUP BY ins2.name,ins2.lng,ins2.lat";
			break;
		}
		System.out.println(sql);

		List<Object[]> list = instrumentDao.common2(sql);
		if (list == null)
			return res;
		Object[] objects = null;
		for (int i = 0; i < list.size(); i++) {
			objects = list.get(i);
			if (i != list.size() - 1) {
				res += objects[1] + "," + objects[0] + "," + objects[2] + "," + objects[3] + "," + loctype + "|";
			} else {
				res += objects[1] + "," + objects[0] + "," + objects[2] + "," + objects[3] + "," + loctype;
			}
		}
		return res;
	}

	@Override
	public List<Object[]> getAllList(String loctype) {
		String sql = null;
		if (loctype.equals("city")) {
			sql = "select city,count(*) from instrument  group by city order by count(*) desc ";
		} else if (loctype.equals("danwei")) {
			sql = "select ins2.name,count(*) from instrument ins1,institution ins2 where ins1.unit_code = ins2.code group by ins2.name order by count(*) desc ";
		}
		return instrumentDao.common2(sql);
	}

	@Override
	public List<Object> getCountList(String quyu, String loctype, String zhibiao) {
		String sql = "";
		if (zhibiao.equals("type")) {
			sql = "select count(*),inst.type_name from instrument ins,instrument_type inst ";
			if (loctype.equals("city"))
				sql += " where ins.type = inst.type_id and city='" + quyu + "'";
			else if (loctype.equals("xian"))
				sql += " where ins.type = inst.type_id and county='" + quyu + "'";
			else if (loctype.equals("danwei"))
				sql += ",institution where ins.type = inst.type_id and ins.unit_code = institution.code and unit_name='" + quyu + "'";
			else
				sql += " where ins.type = inst.type_id ";
			sql += " group by inst.type_name order by count(*) desc ";
		} else {
			sql = "select count(*),instrument." + zhibiao + " from instrument ";
			if (loctype.equals("city"))
				sql += " where city='" + quyu + "'";
			else if (loctype.equals("xian"))
				sql += " where county='" + quyu + "'";
			else if (loctype.equals("danwei"))
				sql += ",institution where unit_code=institution.code and institution.name='" + quyu + "'";
			sql += " group by instrument." + zhibiao + " order by count(*) desc ";
		}
		System.out.println(sql);
		List<Object[]> countlist = instrumentDao.common2(sql);
		String name;
		if (zhibiao.equals("type")) {
			name = "类型";
		} else if (zhibiao.equals("home")) {
			name = "产地";
		} else {
			name = "待定";
		}
		List<Object> ret = new ArrayList<>();
		ret.add(name);
		ret.add(countlist);
		return ret;
	}

	@Override
	public List<Object> findAllType() {
		return instrumentDao.findAllType();
	}

	@Override
	public List<Object[]> getCxTable1Info(String yqmc, String ssdw, String yqmccxfs, String yqlx, String yqcd, String quyu, String loctype) {
		String sql = "select name_zh,ins1.name,ins2.id,ins2.unit_code from institution ins1,instrument ins2,instrument_type inst where ins2.unit_code = ins1.code and ins2.type = inst.type_id ";
		if (loctype.equals("city"))
			sql += " and ins2.city = '" + quyu + "'";
		else if (loctype.equals("xian"))
			sql += " and 大型仪器.所在区县  = '" + quyu + "'";
		else if (loctype.equals("danwei"))
			sql += " and ins1.name= '" + quyu + "'";
		if (!yqmc.equals(""))
			if (yqmccxfs.equals("1"))
				sql += " and name_zh like '" + yqmc + "'";
			else
				sql += " and name_zh like '%" + yqmc + "%'";
		if (!ssdw.equals(""))
			sql += " and ins1.name like '" + ssdw + "'";
		if (!yqlx.equals(""))
			sql += " and inst.type_name  like '" + yqlx + "'";
		if (!yqcd.equals(""))
			sql += " and ins2.home  like '" + yqcd + "'";
		System.out.println(sql);
		return instrumentDao.common2(sql);
	}

	@Override
	public List<Object[]> getCxTable1Info2(String yqmc, String ssdw, String yqmccxfs, String ssdwcxfs, String yqlx, String yqcd, String quyu, String loctype) {
		SolrClient solrClient = SolrUtil.getHttpSolrClient("instrument");
		SolrQuery query = new SolrQuery();
		// 输入查询条件
		if (StringUtils.isNotBlank(yqmc)) {
			switch (yqmccxfs) {
			case "1": // 全匹配
				query.setQuery("instrument_name_s:" + yqmc);
				break;
			case "2": // 模糊匹配
				query.setQuery("instrument_name:" + yqmc);
				break;
			case "3": // 局部全匹配
				query.setQuery("instrument_name_s:*" + yqmc + "*");
				break;
			default:
				query.setQuery("*:*");
				break;
			}
		} else {
			query.setQuery("*:*");
		}
		// 输入过滤条件
		switch (loctype) {
		case "city":
			query.addFilterQuery("institution_city:" + quyu);
			break;
		case "xian":
			query.addFilterQuery("institution_county:" + quyu);
			break;
		case "danwei":
			// 全匹配
			query.addFilterQuery("institution_name_s:" + quyu);
			break;
		}
		System.out.println(ssdwcxfs);
		if (!StringUtils.equals(loctype, "danwei") && StringUtils.isNotBlank(ssdw)) {
			switch (ssdwcxfs) {
			case "1": // 全匹配
				query.addFilterQuery("institution_name_s:" + ssdw);
				break;
			case "2": // 模糊匹配
				query.addFilterQuery("institution_name:" + ssdw);
				break;
			case "3": // 局部全匹配
				query.addFilterQuery("institution_name_s:*" + ssdw + "*");
				break;
			}
		}
		if (StringUtils.isNotBlank(yqlx))
			query.addFilterQuery("instrument_type:" + yqlx);
		if (StringUtils.isNotBlank(yqcd))
			query.addFilterQuery("instrument_home:" + yqcd);
		// 设置分页信息（使用默认的）
		query.setStart(0);
		query.setRows(Integer.MAX_VALUE);
		// 执行查询并返回结果
		System.out.println("getCxTable1Info:" + query);
		QueryResponse response = null;
		try {
			response = solrClient.query(query);
			solrClient.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 获取匹配的所有结果
		List<Object[]> ret = new ArrayList<>();
		Object[] objects = null;
		SolrDocumentList list = response.getResults();
		// 匹配结果总数
		long count = list.getNumFound();
		System.out.println("匹配结果总数:" + count);
		for (SolrDocument doc : list) {
			objects = new Object[4];
			objects[0] = doc.get("instrument_name");
			objects[1] = doc.get("institution_name");
			objects[2] = doc.get("instrument_id");
			objects[3] = doc.get("institution_code");
			ret.add(objects);
		}
		return ret;
	}

	@Override
	public List<Object> getTable1Info(String zhibiao, String zhibiaomc, String quyu, String loctype, String page) {
		String sql = "";
		String sqlnum = "";
		if (zhibiao != null && zhibiao.equals("type")) {
			if (loctype.equals("province")) {
				sql = "select name,name_zh,id,code from instrument,institution,instrument_type where unit_code = code and instrument.type = instrument_type.type_id ";
				sqlnum = "select count(*) from  instrument,institution,instrument_type where unit_code = code and instrument.type = instrument_type.type_id ";
			} else if (loctype.equals("city")) {
				sqlnum = "select count(*)  from instrument,institution,instrument_type where unit_code = code and instrument.type = instrument_type.type_id  and instrument.city ='" + quyu + "'";
				sql = "select name,name_zh,id,code  from instrument,institution,instrument_type   where unit_code=code and instrument.type = instrument_type.type_id  and instrument.city ='" + quyu
						+ "'";
			} else if (loctype.equals("xian")) {
				sqlnum = "select count(*)  from 大型仪器,单位   where 所属单位编号 = 单位编号 and 大型仪器.所在区县  ='" + quyu + "'";
				sql = "select 单位名称 ,仪器名称,仪器编号,单位编号  from 大型仪器,单位   where 所属单位编号 = 单位编号 and 大型仪器.所在区县  ='" + quyu + "'";
			} else if (loctype.equals("danwei")) {
				sqlnum = "select count(*)  from instrument,institution,instrument_type where unit_code = code and instrument.type = instrument_type.type_id  and name  ='" + quyu + "'";
				sql = "select name,name_zh,id,code  from instrument,institution,instrument_type where unit_code=code and instrument.type = instrument_type.type_id  and name  ='" + quyu + "'";
			}
			sqlnum += " and instrument_type.type_name ='" + zhibiaomc + "'";
			sql += " and instrument_type.type_name ='" + zhibiaomc + "'";
		} else {
			if (loctype.equals("province")) {
				sql = "select name,name_zh,id,code from instrument,institution where unit_code = code ";
				sqlnum = "select count(*) from  instrument,institution where unit_code = code ";
			} else if (loctype.equals("city")) {
				sqlnum = "select count(*)  from instrument,institution where unit_code = code and instrument.city ='" + quyu + "'";
				sql = "select name,name_zh,id,code  from instrument,institution   where unit_code=code and instrument.city ='" + quyu + "'";
			} else if (loctype.equals("xian")) {
				sqlnum = "select count(*)  from 大型仪器,单位   where 所属单位编号 = 单位编号 and 大型仪器.所在区县  ='" + quyu + "'";
				sql = "select 单位名称 ,仪器名称,仪器编号,单位编号  from 大型仪器,单位   where 所属单位编号 = 单位编号 and 大型仪器.所在区县  ='" + quyu + "'";
			} else if (loctype.equals("danwei")) {
				sqlnum = "select count(*)  from instrument,institution where unit_code = code and name  ='" + quyu + "'";
				sql = "select name,name_zh,id,code  from instrument,institution where unit_code=code and name  ='" + quyu + "'";
			}
			if (!zhibiao.equals("")) {
				sqlnum += " and instrument." + zhibiao + "='" + zhibiaomc + "'";
				sql += " and instrument." + zhibiao + "='" + zhibiaomc + "'";
			}
		}
		System.out.println(sql);

		int curpage = 1;
		if (page != null)
			curpage = Integer.valueOf(page);
		// 总页数
		int totalpage = 0;
		// 下一页
		int nextpage = 0;
		// 上一页
		int lastpage = 0;
		// 每页显示条数
		int pagenum = 10;
		// 数据总条数
		int total = 0;
		List<Object> rsnum = instrumentDao.common1(sqlnum);
		if (rsnum != null) {
			if (Integer.valueOf(rsnum.get(0).toString()) % pagenum != 0)
				totalpage = Integer.valueOf(rsnum.get(0).toString()) / pagenum + 1;
			else
				totalpage = Integer.valueOf(rsnum.get(0).toString()) / pagenum;
			total = Integer.valueOf(rsnum.get(0).toString());
		}
		if (curpage == 1)
			lastpage = 1;
		else
			lastpage = curpage - 1;
		if (curpage < totalpage)
			nextpage = curpage + 1;
		else {
			nextpage = totalpage;
			pagenum = total - pagenum * (totalpage - 1);
		}
		// 显示区间
		int beginnum = (curpage - 1) * 10;
		int endnum = beginnum + pagenum;

		List<Object[]> rs = instrumentDao.common2(sql);
		List<Object> ret = new ArrayList<>();
		ret.add(totalpage);
		ret.add(total);
		ret.add(curpage);
		ret.add(nextpage);
		ret.add(lastpage);
		ret.add(pagenum);
		ret.add(beginnum);
		ret.add(endnum);
		ret.add(rs);
		return ret;
	}

	@Override
	public List<Object[]> getTable2Info(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append("name_zh,");
		sb.append("name_en,");
		sb.append("depart_name,");
		sb.append("ins1.contact_name,");
		sb.append("ins1.contact_phone,");
		sb.append("contact_email,");
		sb.append("contact_fax,");
		sb.append("ins1.city,");
		sb.append("org_value,");
		sb.append("industry_type,");
		sb.append("inst.type_name,");
		sb.append("name,");
		sb.append("code");
		String sql = "select " + sb.toString() + " from instrument ins1,institution ins2,instrument_type inst where ins2.code=ins1.unit_code and ins1.type = inst.type_id and id='" + id + "'";
		return instrumentDao.common2(sql);
	}
	
	@Override
	public List<Object[]> getInstrumentByResource(String id,String resource) {
		String sql = null;
		switch (resource) {
		case "institution":
			//sql = "SELECT ins1.id,ins1.name_zh FROM instrument ins1,institution ins2 WHERE ins1.unit_code = ins2.code AND ins2.code = "+id+" LIMIT 0,20";
			sql = "SELECT ins1.id,ins1.name_zh FROM instrument ins1,institution ins2 WHERE ins1.unit_code = ins2.code AND ins2.code = "+id;
			break;
		case "base":
			//sql = "SELECT ins1.id,ins1.name_zh FROM instrument ins1,institution ins2 WHERE ins1.unit_code = ins2.code AND ins2.code = "+id+" LIMIT 0,10";
			sql = "SELECT ins1.id,ins1.name_zh FROM instrument ins1,institution ins2 WHERE ins1.unit_code = ins2.code AND ins2.code = "+id;
			break;
		default:
			break;
		}
		return instrumentDao.common2(sql);
	}
	
	@Override
	public Map<String, Object> getInstrumentDetail(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append("ins1.manage_code,");
		sb.append("ins1.name_zh,");
		sb.append("ins1.name_en,");
		sb.append("ins1.depart_name,");
		sb.append("ins1.contact_name,");
		sb.append("ins1.contact_phone,");
		sb.append("ins1.contact_email,");
		sb.append("ins1.contact_fax,");
		sb.append("ins1.contact_addr,");
		sb.append("ins1.contact_zipcode,");
		sb.append("ins1.buy_time,");
		sb.append("ins1.is_import,");
		sb.append("ins1.home,");
		sb.append("ins1.manufacturer,");
		sb.append("ins1.model,");
		sb.append("ins1.city,");
		sb.append("ins1.org_value,");
		sb.append("ins1.rmb_value,");
		sb.append("ins1.industry_type,");
		sb.append("inst.type_name,");
		sb.append("ins1.base_name,");
		sb.append("ins1.base_type,");
		sb.append("ins1.weekday,");
		sb.append("ins1.status,");
		sb.append("ins2.code,");
		sb.append("ins2.name");
		String sql = "select " + sb.toString() + " from instrument ins1,institution ins2,instrument_type inst where ins2.code=ins1.unit_code and ins1.type = inst.type_id and id='" + id + "'";
		List<Object[]> ret = instrumentDao.common2(sql);
		Map<String, Object> map = new LinkedHashMap<>();
		if(CollectionUtils.isNotEmpty(ret)){
			Object[] objects = ret.get(0);
			map.put("管理编码", objects[0]);
			map.put("中文名称", objects[1]);
			map.put("英文名称", objects[2]);
			map.put("上级主管部门", objects[3]);
			map.put("联系人姓名", objects[4]);
			map.put("联系人电话", objects[5]);
			map.put("联系人邮件", objects[6]);
			map.put("联系人传真", objects[7]);
			map.put("联系人地址", objects[8]);
			map.put("联系人邮编", objects[9]);
			map.put("购买时间", objects[10]);
			map.put("是否进口", objects[11]);
			map.put("产地", objects[12]);
			map.put("制造商", objects[13]);
			map.put("型号", objects[14]);
			map.put("所在市", objects[15]);
			map.put("原值", objects[16]);
			map.put("人民币价值", objects[17]);
			map.put("产业类别", objects[18]);
			map.put("类型", objects[19]);
			map.put("所属基地", objects[20]);
			map.put("基地类型", objects[21]);
			map.put("运行状态", objects[22]);
			map.put("工作时间", objects[23]);
			map.put("机构编码", objects[24]);
			map.put("所属机构", objects[25]);
		}
		return map;
	}
	
	public List<Object[]> getRelationInstrument(String id){
		//String sql = "SELECT id,name_zh FROM instrument ins WHERE unit_code = (SELECT unit_code FROM instrument WHERE id = "+id+") AND id != "+id+" LIMIT 0,10";
		String sql = "SELECT id,name_zh FROM instrument ins WHERE unit_code = (SELECT unit_code FROM instrument WHERE id = "+id+") AND id != "+id;
		return  instrumentDao.common2(sql);
	}


	@Override
	public String[][] getCp(String totalid) {
		// 哈尔滨市@city;双鸭山市@city;伊春市@city
		String[] id = totalid.split(";");
		String[][] data = new String[id.length][3];
		List<Object[]> query = null;
		for (int i = 0; i < id.length; i++) {
			if (StringUtils.isNotBlank(id[i])) {
				String[] quyu = id[i].split("@");
				String sql = "select count(*),sum(cast(org_value as decimal(20,2))) from instrument ins1,institution ins2 where ins1.unit_code = ins2.code ";
				if (quyu[1].equals("city"))
					sql += "and ins2.city ='" + quyu[0] + "'";
				if (quyu[1].equals("xian"))
					sql += "and ins2.county ='" + quyu[0] + "'";
				query = instrumentDao.common2(sql);
				if (query != null && query.size() != 0) {
					Object[] objects = query.get(0);
					data[i][0] = quyu[0];

					if (objects[0] != null && StringUtils.isNotBlank(objects[0].toString()))
						data[i][1] = objects[0].toString();
					else
						data[i][1] = "0";
					if (objects[1] != null && StringUtils.isNotBlank(objects[1].toString())) {
						Float millon = Float.parseFloat(objects[1].toString()) / 100;
						data[i][2] = millon.toString();
					} else
						data[i][2] = "0";
				}
			}
		}
		return data;
	}

	public String getSum(String zhibiao) {
		Map<String, Long> data = getCount("", "", zhibiao);
		Set<Map<String, Object>> set = new LinkedHashSet<>();
		Map<String, Object> map;
		for (Map.Entry<String, Long> entry : data.entrySet()) {
			map = new HashMap<>();
			map.put("name", entry.getKey());
			map.put("value", entry.getValue());
			set.add(map);
		}
		return JacksonUtil.toJSon(set);
	}

	@Override
	public List<Object[]> getFb() {
		String sql = "SELECT ins2.lng,ins2.lat,COUNT(*),code FROM instrument ins1,institution ins2 WHERE ins1.unit_code = ins2.code GROUP BY code";
		return instrumentDao.common2(sql);
	}

	@Override
	public List<Map<String, Object>> getFb2() {
		String sql = "SELECT ins2.city,COUNT(*) FROM project ins1,institution ins2,city WHERE ins1.unit_code = ins2.code AND ins2.city = city.city_name GROUP BY ins2.city ";
		List<Object[]> list = instrumentDao.common2(sql);

		List<Map<String, Object>> ret = new ArrayList<>();
		Map<String, Object> map = null;
		for (Object[] objects : list) {
			map = new HashMap<>();
			map.put("name", objects[0]);
			map.put("value", objects[1]);
			ret.add(map);
		}
		return ret;
	}

	@Override
	public String getCitySum() {
		String sql = "select city,count(*) from Instrument group by city ORDER BY count(*) DESC";
		List<Object[]> list = instrumentDao.common2(sql);
		/// 排序的
		// Set<Map<String, Object>> set = new LinkedHashSet<>();
		Set<Map<String, Object>> set = new HashSet<>();
		Map<String, Object> map;
		for (Object[] objects : list) {
			String name = objects[0].toString();
			// 数据表中有填"其他市"，有没有填的
			if (name.equals("其他市") || name.equals(""))
				continue;
			map = new HashMap<String, Object>();
			Integer value = 0;
			try {
				value = Integer.valueOf(objects[1].toString());
			} catch (Exception e) {
				e.printStackTrace();

			}
			map.put("name", name);
			map.put("value", value);
			set.add(map);
		}
		return JacksonUtil.toJSon(set);
	}

	// --backup--
	public String getCitySum2() {
		String sql = "select city,count(*) from Instrument group by city";
		List<Object[]> list = instrumentDao.common2(sql);
		Set<Map<String, Object>> set = new TreeSet<Map<String, Object>>(new Comparator<Map<String, Object>>() {
			@Override
			public int compare(Map<String, Object> o1, Map<String, Object> o2) {
				try {
					int i = Integer.valueOf(o1.get("value").toString()).compareTo(Integer.valueOf(o2.get("value").toString()));
					return i > 0 ? 1 : i < 0 ? -1 : 1;
				} catch (Exception e) {
					e.printStackTrace();
					return 1;
				}
			}
		});
		Map<String, Object> map;
		for (Object[] objects : list) {
			String name = objects[0].toString();
			// 数据表中有填"其他市"，有没有填的
			if (name.equals("其他市") || name.equals(""))
				continue;
			map = new HashMap<String, Object>();
			Integer value = 0;
			try {
				value = Integer.valueOf(objects[1].toString());
			} catch (Exception e) {
				e.printStackTrace();

			}
			map.put("name", name);
			map.put("value", value);
			set.add(map);
		}
		return JacksonUtil.toJSon(set);
	}

}
